package org.springmole.business.dao.impl;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

import org.springmole.business.bean.ColumnResultData;
import org.springmole.business.bean.DbConnection;
import org.springmole.business.bean.DbConnectionResultData;
import org.springmole.business.bean.QueryRequest;
import org.springmole.business.bean.Result;
import org.springmole.business.bean.ResultStatus;
import org.springmole.business.bean.SchemaResultData;
import org.springmole.business.bean.TableResultData;
import org.springmole.business.bean.query.QueryResultData;
import org.springmole.business.bean.query.SqlOrderBy;
import org.springmole.business.dao.SpringMoleDao;
import org.w3c.dom.Document;
import org.w3c.dom.Element;


/**
 * This abstract implementation only provide a {@link AbstractSpringMoleDaoImpl#IMPLEMENTATION_BEAN_NAME_PREFIX} which is use
 * by each concrete implementation to create the full bean name.<br/>
 * The suffix of the bean name must be the second token of the corresponding connection URL.
 * 
 * <p>As example, this URL:</p>
 * 
 * <code>jdbc:postgresql://localhost/test</code>
 * 
 * <p>will load this DAO:</p>
 * 
 * <code>{@link org.springframework.stereotype.Repository}(AbstractSpringMoleDaoImpl.IMPLEMENTATION_BEAN_NAME_PREFIX + "postgresql")</code> 
 */
public abstract class AbstractSpringMoleDaoImpl extends AbstractJDBCDaoImpl implements SpringMoleDao {
	public final static String IMPLEMENTATION_BEAN_NAME_PREFIX = "springMoleDao-";
	
	public final static String ORDER_BY_SQL = "ORDER BY";
	public final static String ASC_ORDER_SQL = "ASC";
	public final static String DESC_ORDER_SQL = "DESC";
	
	
	protected List<SchemaResultData> getSchemaResultDataList(DatabaseMetaData databaseMetaData) throws SQLException {
		ResultSet resultSetSchema = databaseMetaData.getSchemas();
		
		List<SchemaResultData> schemaResultDataList = new ArrayList<SchemaResultData>();
		while(resultSetSchema.next()) {
			SchemaResultData schemaResultData = new SchemaResultData();
			schemaResultData.setName(resultSetSchema.getString("TABLE_SCHEM"));
			schemaResultDataList.add(schemaResultData);
		}
		resultSetSchema.close();
		
		return schemaResultDataList;
	}
	

	protected List<TableResultData> getTableResultDataList(DatabaseMetaData databaseMetaData, String schemaName) throws SQLException {
		List<TableResultData> tableResultDataList = new ArrayList<TableResultData>();
		String[] tableTypeArray = {"TABLE"};
		
		ResultSet resultSetTable = databaseMetaData.getTables(null, schemaName, null, tableTypeArray);
		while (resultSetTable.next()) {
			TableResultData tableResultData = new TableResultData();
			String tableName = resultSetTable.getString("TABLE_NAME");
			tableResultData.setName(tableName);
			
			ResultSet columnResultSet = databaseMetaData.getColumns(null, schemaName, tableName, null);
			while(columnResultSet.next()) {
				ColumnResultData columnResultData = new ColumnResultData();
				columnResultData.setName(columnResultSet.getString("COLUMN_NAME"));
				columnResultData.setSqlType(columnResultSet.getInt("DATA_TYPE"));
				
				tableResultData.getColumnResultDataList().add(columnResultData);
			}
			
			tableResultDataList.add(tableResultData);
		}
		
		return tableResultDataList;
	}
	
	
	@Override
	public Result<DbConnectionResultData> openDbConnection(DbConnection dbConnection) {
		
		Result<DbConnectionResultData> result = new Result<DbConnectionResultData>();
		ResultStatus resultStatus = new ResultStatus(); 
		DbConnectionResultData resultData = new DbConnectionResultData();
		
		Connection connection = null;
		try {
			connection = openConnection(dbConnection);
			DatabaseMetaData databaseMetaData = connection.getMetaData();

			resultData.setSchemaResultDataList(getSchemaResultDataList(databaseMetaData));
			result.setResultData(resultData);
			resultStatus.setSuccess(true);
		} catch (SQLException e) {
			resultStatus.parseException(e);
		} finally {
			if (connection != null) {
				try {
					connection.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		
		result.setResultStatus(resultStatus);
		
		return result;
	}




	@Override
	public Result<SchemaResultData> openSchema(DbConnection dbConnection,	String schemaName) {
		Result<SchemaResultData> result = new Result<SchemaResultData>();
		Connection connection = null;
		try {
			connection = openConnection(dbConnection);

			SchemaResultData schemaResultData = new SchemaResultData();
			schemaResultData.setTableResultDataList(getTableResultDataList(connection.getMetaData(), schemaName));
			result.setResultData(schemaResultData);
			
			result.getResultStatus().setSuccess(true);
		} catch (SQLException e) {
			result.getResultStatus().parseException(e);
		} finally {
			if (connection != null) {
				try {
					connection.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		
		return result;
	}
	
	
	@Override
	public Result<QueryResultData> executeQuery(QueryRequest queryRequest, Document document) {
		Result<QueryResultData> result = new Result<QueryResultData>();
		Connection connection = null;
		try {
			connection = openConnection(
					queryRequest.getUrl(), 
					queryRequest.getUserName(), 
					queryRequest.getPassword());

			QueryResultData queryResultData = new QueryResultData("queryresult");
			
			SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss");
			
      		// Create a scrollable ResultSet for paging
			Statement statement = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
			
			List<SqlOrderBy> sqlOrderByList = new ArrayList<SqlOrderBy>();
			sqlOrderByList.add(new SqlOrderBy(queryRequest.getSortField(), queryRequest.getSortOrder()));
			
			ResultSet resultSet = statement.executeQuery(cleanQueryString(queryRequest.getQuery()) + getSqlOrderBy(sqlOrderByList));
			
			// Scroll to last row
			resultSet.last();
			
			// Set row number as total attribute 
			queryResultData.setTotal(new Long(resultSet.getRow()));
			
			// Replace the cursor at the current page index
			resultSet.absolute(queryRequest.getRowIndex().intValue());
			
			ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
			int clolumnCount = resultSetMetaData.getColumnCount();
			
			while(resultSet.next()) {
				Element rowElement = document.createElement("Row");
				
				for(int c=1; c<=clolumnCount; c++) {
					String value = "";
					if (resultSetMetaData.getColumnType(c) == Types.TIMESTAMP) {
						value = simpleDateFormat.format(resultSet.getTimestamp(c));
					} else {
						value = resultSet.getString(c);
					}
					rowElement.setAttribute(resultSetMetaData.getColumnName(c), value);
				}
				
				queryResultData.getQueryRowList().add(rowElement);
			}
			
			result.setResultData(queryResultData);
			
			result.getResultStatus().setSuccess(true);
		} catch (SQLException e) {
			result.getResultStatus().parseException(e);
		} finally {
			if (connection != null) {
				try {
					connection.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		
		return result;
	}
	
	
	@Override
	public String getSqlOrderBy(List<SqlOrderBy> sqlOrderByList) {
		if (sqlOrderByList.size() == 0) {
			return "";
		}
		
		StringBuilder stringBuilder = new StringBuilder(" ");
		stringBuilder.append(ORDER_BY_SQL);
		
		int cnt = 0;
		int size = sqlOrderByList.size();
		for (SqlOrderBy sqlOrderBy : sqlOrderByList) {
			stringBuilder.append(" ");
			stringBuilder.append(sqlOrderBy.getSortField());
			stringBuilder.append(" ");
			stringBuilder.append(parseOrder(sqlOrderBy.getSortOrder()));
			
			cnt++;
			if (cnt < size) {
				stringBuilder.append(",");
			}
		}
		
		return stringBuilder.toString();
	}
	
	
	protected String parseOrder(String orderFromViewLayer) {
		if (orderFromViewLayer != null && !orderFromViewLayer.equals("")) {
			if (ASC_ORDER_SQL.equalsIgnoreCase(orderFromViewLayer.trim())) {
				return ASC_ORDER_SQL;
			}
			if (DESC_ORDER_SQL.equalsIgnoreCase(orderFromViewLayer.trim())) {
				return DESC_ORDER_SQL;
			}
		}
		return ASC_ORDER_SQL;
	}
	

	// TODO: remove last ";"
	protected String cleanQueryString(String query) {
		query = query.trim();
		
		return query;
	}
		
		
}
